package com.raja.servingStation.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.raja.servingStation.common.constant.Constants;
import com.raja.servingStation.common.constant.MysqlPageVo;
import com.raja.servingStation.datasources.RepairSql2oTemp;
import com.raja.servingStation.entity.Notice;
import com.raja.servingStation.entity.NoticeAttach;
import com.raja.servingStation.entity.vo.NoticeDetail;
import com.raja.servingStation.entity.vo.NoticeReadingDetail;
import com.raja.servingStation.entity.vo.NoticeState;
import com.raja.servingStation.entity.vo.NoticeView;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.util.List;


/**
 * 公文发布Dao
 * on 2017/9/30.
 */
@Repository
public class NoticeViewDao {
    private static final Logger log = LoggerFactory.getLogger(NoticeViewDao.class);

    @Autowired
    private RepairSql2oTemp sql2oTemp;


    /**
     * 公文列表总数
     * @param paramMap
     * @return
     */
    public Long cntNoticeSubscribeList(JSONObject paramMap) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(ns.ID) ");
        sql.append(" from T_NOTICE_SUBSCRIBE ns ");
        sql.append(" LEFT JOIN T_NOTICE n on n.ID = ns.NOTICE_ID ");
        sql.append(" WHERE ns.STATION_ID = :stationId AND ns.STATE in(2,3) AND n.FLAG=1 ");


//        if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//            sql.append(" and ( instr(o.ORDER_CODE, :keyword) > 0 or instr(o.CAR_NO, :keyword) > 0 or instr(o.DRIVER_PHONE, :keyword) > 0 ) ");
//        }
//        if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//            sql.append(" and ( o.ORDER_CODE = :keyword ");
//            sql.append(" or o.CAR_NO = :keyword ");
//            sql.append(" or o.DRIVER_PHONE = :keyword )");
//        }
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());

            query.addParameter("stationId", paramMap.getString("stationId"));
//            if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//                query.addParameter("keyword", paramMap.get("keyWord"));
//            }
            return (Long)query.executeScalar();
        }
    }


    /** todo
     * 公文订阅列表
     * @param pageVO
     * @return
     * @throws Exception
     */
    public List<NoticeView> queryNoticeSubscribeListByPage(JSONObject paramMap, MysqlPageVo pageVO) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select n.ID, n.TITLE, n.CONTENT, n.PUBLISH_DATE, n.PUBLISHER_ID, ns.STATE ");
        sql.append(" from T_NOTICE_SUBSCRIBE ns ");
        sql.append(" LEFT JOIN T_NOTICE n on n.ID = ns.NOTICE_ID ");
        sql.append(" WHERE ns.STATION_ID = :stationId AND ns.STATE in(2,3) AND n.FLAG=1 ");

        if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
            sql.append(" and ( instr(o.ORDER_CODE, :keyword) > 0 or instr(o.CAR_NO, :keyword) > 0 or instr(o.DRIVER_PHONE, :keyword) > 0 ) ");
        }
//        if (!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//            sql.append(" and ( o.ORDER_CODE = :keyword ");
//            sql.append(" or o.CAR_NO = :keyword ");
//            sql.append(" or o.DRIVER_PHONE = :keyword )");
//        }
//        sql = sql + " order by bb.WRITE_TIME desc";
//        sql.append(" order by o.UPDATE_TIME desc ");
        String sqlfy = Constants.mysql_page_pre_sql+sql.toString()+Constants.mysql_page_end_sql;
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sqlfy);
            if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
                query.addParameter("keyword", paramMap.get("keyWord"));
            }
//            if(paramMap.get("state") != null) {
//                query.addParameter("state",paramMap.get("state"));
//            }
            query.addParameter("stationId", paramMap.getString("stationId"));
            query.addParameter("limit",pageVO.getPageSize().intValue());
            query.addParameter("offset",pageVO.getOffset().intValue());
            List<NoticeView> noticeViews = query.setAutoDeriveColumnNames(true).executeAndFetch(NoticeView.class);
            return noticeViews;
        }
    }


    /**
     * 获取公文详情
     * @param noticeId
     * @return
     * @throws Exception
     */
    public NoticeDetail queryNoticeDetail(Long noticeId) throws Exception {
        final String sql = "SELECT * FROM t_notice WHERE ID = :noticeId";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("noticeId", noticeId);
            return query.setAutoDeriveColumnNames(true).executeAndFetchFirst(NoticeDetail.class);
        }
    }

    /**
     * 获取单个公文的多个附件
     * @param noticeId
     * @return
     * @throws Exception
     */
    public List<NoticeAttach> queryNoticeAttachs(Long noticeId) throws Exception {
        final String sql = " select * from T_NOTICE_ATTACH where NOTICE_ID=:noticeId ";
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("noticeId", noticeId);
            List<NoticeAttach> noticeAttachs = query.setAutoDeriveColumnNames(true).executeAndFetch(NoticeAttach.class);
            return noticeAttachs;
        }
    }











    /**
     * 公告阅读明细
     * @param noticeId
     * @return
     * @throws Exception
     */
    public List<NoticeReadingDetail> queryNoticeReadingDetail(Long noticeId) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ss.ID STATION_ID, ss.NAME, ss.ABBNAME, ns.STATE FROM T_SERVICING_STATION ss ");
        sql.append("RIGHT JOIN T_NOTICE_SUBSCRIBE ns on ss.ID = ns.STATION_ID ");
        sql.append("WHERE ns.NOTICE_ID = :noticeId ");
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            query.addParameter("noticeId", noticeId);
            return query.setAutoDeriveColumnNames(true).executeAndFetch(NoticeReadingDetail.class);
        }
    }


    /**
     * 统计公文状态数量
     * 1、未发送
     * 2、已发送
     * 3、未阅读
     * 4、已阅读
     * @param noticeId
     * @return
     */
    public List<NoticeState> getNoticeStates(Long noticeId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT STATE, COUNT(STATE) STATE_Num ");
        sql.append("FROM T_NOTICE_SUBSCRIBE ");
        sql.append("WHERE NOTICE_ID = :noticeId ");
        sql.append("GROUP BY STATE ");

        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            query.addParameter("noticeId", noticeId);
            return query.setAutoDeriveColumnNames(true).executeAndFetch(NoticeState.class);
        }
    }


    //--------------------------------------发公文接口-------------------------------------------
    /**
     * 公文发布接口
     * @param body
     */
    public void publishNotice(JSONObject body) {
        try (Connection con = sql2oTemp.getSql2o().beginTransaction()) {
            //发公主表
            Long noticeId = this.addNotice(con, body);

            //公告订阅表
            String[] stationIds = body.getString("stationIds").split(",");
            for (int i = 0; i < stationIds.length; i++) {
                this.addNoticeSubscribe(con, noticeId, stationIds[i]);
            }

            //公告附件表
            if (null != body.getString("attachs")) {
                List<NoticeAttach> noticeAttachs = JSONArray.parseArray(body.getString("attachs"), NoticeAttach.class);
                for (int i = 0; i < noticeAttachs.size(); i++) {
                    NoticeAttach noticeAttach = noticeAttachs.get(i);
                    noticeAttach.setNoticeId(noticeId);
                    this.addNoticeAttach(con, noticeAttach);
                }
            }
            con.commit();
        } finally {
            sql2oTemp.setAutoCommit(true);
        }
    }


    private Long addNotice(Connection con, JSONObject body){
        StringBuilder sql = new StringBuilder("insert into T_NOTICE");
        sql.append(" (TITLE,CONTENT) values( ");
        sql.append(" :title, :content) ");
        log.debug(sql.toString());
//        con.createQuery(sql.toString()).bind(notice).executeUpdate();
        con.createQuery(sql.toString())
            .addParameter("title", body.getString("title"))
            .addParameter("content", body.getString("content"))
            .executeUpdate();

        return con.createQuery(" SELECT MAX(id) FROM T_NOTICE ")
                .executeScalar(Long.class);
    }

    //公告订阅表
    private void addNoticeSubscribe(Connection con, Long noticeId, String stationId) {
        StringBuilder sql = new StringBuilder("insert into T_NOTICE_SUBSCRIBE");
        sql.append(" (NOTICE_ID, STATION_ID, STATE) values( ");
        sql.append(" :noticeId, :stationId, 1) ");

        con.createQuery(sql.toString())
                .addParameter("noticeId", noticeId)
                .addParameter("stationId", stationId)
                .executeUpdate();
    }

    //公告附件表
    private void addNoticeAttach(Connection con, NoticeAttach noticeAttach) {
        StringBuilder sql = new StringBuilder("insert into T_NOTICE_ATTACH");
        sql.append(" (NOTICE_ID, CLOUD_ID, FILE_NAME) values( ");
        sql.append(" :noticeId, :attachId, :fileName) ");

        con.createQuery(sql.toString())
                .addParameter("noticeId", noticeAttach.getNoticeId())
                .addParameter("attachId", noticeAttach.getCloudId())
                .addParameter("fileName", noticeAttach.getFileName())
                .executeUpdate();
    }
    //--------------------------------------发公文接口-------------------------------------------
}
